package com.example.utils.File;

import com.alibaba.druid.util.StringUtils;
import com.example.entity.Student;
import com.example.entity.Subject;
import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

/**
 * @author： wzz
 * @date： 2021-04-01 17:06
 */

public class ExcelUtil {


    //是否符合2003Excel
    public static boolean isExcel2003(String filePath){
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    //是否符合2007Excel
    public static boolean isExcel2007(String filePath){
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    public static List<Student> readStuExcel(MultipartFile file) throws IOException {

        List<Student> studentList = new ArrayList<>();
        String fileName = file.getOriginalFilename();
        String suffix = fileName.substring(fileName.lastIndexOf(".") + 1);
        InputStream ins = file.getInputStream();
        Workbook wb = null;
        if (suffix.equals("xlsx")) {
            wb = new XSSFWorkbook(ins);
        } else {
            wb = new HSSFWorkbook(ins);
        }
        Sheet sheet = wb.getSheetAt(0);//获取工作表
        if (sheet != null) {
            for (int line = 1; line <= sheet.getLastRowNum(); line++) {//遍历工作表
                Row row = sheet.getRow(line);
                if (row == null) {//空行跳过
                    continue;
                }
                row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);//第一行
                String num = row.getCell(0).getStringCellValue();
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);//第二行

                String name = row.getCell(1).getStringCellValue();
                String sex = row.getCell(2).getStringCellValue();
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                String phone = row.getCell(3).getStringCellValue();
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                String classes = row.getCell(4).getStringCellValue();
//                row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
//                String password = row.getCell(5).getStringCellValue();
                // meetingtype.setCreatedate(creatdate);
                Student student = new Student(num, name, classes, phone, sex,num, null, 0);
                studentList.add(student);
            }
            //最后要关闭流
            try {
                wb.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
         }
        return studentList;
    }

    //导入选择题
    public static List<Subject> readSubjects(MultipartFile file){
        Workbook workbook = null;
        List<Subject> list = new ArrayList<>();
        if (ExcelUtil.isExcel2007(file.getOriginalFilename())){
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        if (ExcelUtil.isExcel2003(file.getOriginalFilename())){
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        Sheet sheet = workbook.getSheetAt(0);
//        从第一个数据开始读
        for (int i = sheet.getFirstRowNum() + 1; i <= sheet.getLastRowNum(); i++){
            Row row = sheet.getRow(i);

            //获取题号
            String questionNo = getCellValue(row.getCell(0));
            //获取题干
            String stem = getCellValue(row.getCell(1));
            //获取选项一
            String option_one = getCellValue(row.getCell(2));
            //获取选项二
            String option_two = getCellValue(row.getCell(3));
            //获取选项三
            String option_three = getCellValue(row.getCell(4));
            //获取选项四
            String option_four = getCellValue(row.getCell(5));
            //获取答案
            String answer = getCellValue(row.getCell(6));
            //获取难易度
            String facility = getCellValue(row.getCell(7));
            //知识点
            String point = getCellValue(row.getCell(8));
            //题型
            String type  = getCellValue(row.getCell(9));

           Subject subject = new Subject("","",questionNo,point,facility,stem,option_one,option_two,option_three,option_four,answer,type);

            list.add(subject);

        }

        //最后要关闭流
        try {
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        System.out.println(list);
        return list;
    }




    public static void writeExcel(){
        HSSFWorkbook workbook = new HSSFWorkbook();
        DocumentSummaryInformation info = workbook.getDocumentSummaryInformation();
        info.setCategory("育人贡献奖投票统计");
        HSSFSheet sheet = workbook.createSheet("投票统计");
        HSSFRow header = sheet.createRow(0);
        HSSFCell cell0 = header.createCell(0);
        HSSFCell cell1 = header.createCell(1);
        HSSFCell cell2 = header.createCell(2);
        HSSFCell cell3 = header.createCell(3);
        HSSFCell cell4 = header.createCell(4);
        cell0.setCellValue("工号");
        cell1.setCellValue("姓名");
        cell2.setCellValue("学院");
        cell3.setCellValue("学生投票数");
        cell4.setCellValue("教师投票数");
    }

    /**
     * 拿到不同类型单元格中的值
     * 1. 字符串: 字符串
     * 2. 布尔: toString
     * 3. 数值(double): 格式化后的字符串
     * @param cell 获取的单元格
     * @return 单元格中的值
     */
    private static String getCellValue(Cell cell) {
        String resultValue = "";
        // 判空
        if (Objects.isNull(cell)) {
            return resultValue;
        }

        // 拿到单元格类型
        int cellType = cell.getCellType();
        switch (cellType) {
            // 字符串类型
            case Cell.CELL_TYPE_STRING:
                resultValue = StringUtils.isEmpty(cell.getStringCellValue()) ? "" : cell.getStringCellValue().trim();
                break;
            // 布尔类型
            case Cell.CELL_TYPE_BOOLEAN:
                resultValue = String.valueOf(cell.getBooleanCellValue());
                break;
            // 数值类型
            case Cell.CELL_TYPE_NUMERIC:
                resultValue = new DecimalFormat("#.######").format(cell.getNumericCellValue());
                break;
            // 取空串
            default:
                break;
        }
        return resultValue;
    }

}
